<?php
/**
 * 验证盈亏汇总
 * Created by PhpStorm.
 * Author: 张志祥
 * QQ: 786811688
 * Phone: 13306281319
 * Date: 2017/5/21
 * Time: 12:40
 */

// 机构流水4汇总（盈亏） = -客户流水345汇总（外扣）
// 机构流水345汇总（收入汇总，返点平仓异常） = -客户流水345汇总（内扣）
// 机构流水4汇总（盈亏） = -历史订单表的盈亏汇总（外扣）
// 机构流水4汇总（盈亏）+ 机构流水35汇总（手续费） = -历史订单表的盈亏汇总（内扣）

// 客户流水345汇总 = 历史订单盈亏汇总
// 客户流水5汇总 = 异常订单金额汇总

// 机构表盈亏汇总 = 客户表亏损金额汇总 - 客户表盈利金额汇总（外扣）
// 机构表盈亏汇总 + 机构流水35汇总（手续费） = 客户表亏损金额汇总 - 客户表盈利金额汇总（内扣）

// 机构表盈亏汇总 = 机构流水4当日汇总
// 客户表亏损金额汇总 - 客户表盈利金额汇总 = 客户流水345当日汇总

// 分层机构流水4盈亏汇总 = -相关历史订单盈亏汇总（外扣）
// 分层机构流水4盈亏汇总 = -相关历史订单盈亏汇总 - 相关订单的机构手续费（内扣）

include 'pub.php'; // 公共函数

// 当日时间定义
$start_time = json_decode($gpstr->get('trading_times')['val'], true)['start_time']; // 开盘时间
$today_start = strtotime(date('Y-m-d')) + $start_time * 3600; // 当日开始时间
// 内扣、外扣
$fee_in = json_decode($gpstr->get('trading_rules')['val'], true)['fee_in']; // 手续费内扣外扣，0外扣，1内扣
// 手续费比例
$fee_ratio = json_decode($gpstr->get('trading_rules')['val'], true)['service_fee'] / 100; // 扣点比例


// 机构流水4汇总（盈亏）
$sql = 'SELECT Sum(zzx_balanceinfo_jg.atm) as pl FROM zzx_balanceinfo_jg';
$where = ' WHERE zzx_balanceinfo_jg.dealtype = 4 ';
$res = $my_conn->query($sql . $where);
$pl_jqls = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "机构流水4汇总（盈亏）：$pl_jqls\n";

// 机构流水345汇总（收入汇总，返点平仓异常，内扣）
$where = ' WHERE zzx_balanceinfo_jg.dealtype BETWEEN 3 AND 5 ';
$res = $my_conn->query($sql . $where);
$jqls = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "机构流水345汇总（收入汇总，返点平仓异常，内扣）：$jqls\n";

// 机构流水35汇总（手续费）
$sql = 'SELECT Sum(zzx_balanceinfo_jg.atm) as pl FROM zzx_balanceinfo_jg WHERE zzx_balanceinfo_jg.dealtype = 3';
$sql2 = 'SELECT Sum(zzx_balanceinfo_jg.atm) as pl FROM zzx_balanceinfo_jg WHERE zzx_balanceinfo_jg.dealtype = 5';
$res = $my_conn->query($sql);
$res2 = $my_conn->query($sql2);
$fee = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'] + mysqli_fetch_array($res2, MYSQLI_ASSOC)['pl'];
echo "机构流水表手续费汇总：$fee\n";

// 机构流水4当日汇总
$where = " WHERE zzx_balanceinfo_jg.dealtype = 4  AND createtime >=$today_start";
$sql = 'SELECT Sum(zzx_balanceinfo_jg.atm) as pl FROM zzx_balanceinfo_jg'.$where;
$res = $my_conn->query($sql);
$pl_jqls_today = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "机构流水表当日盈亏汇总：$pl_jqls_today\n";


// 客户流水345汇总（包括下单、平仓、异常）
$sql = 'SELECT Sum(zzx_balanceinfo_kh.atm) as pl FROM zzx_balanceinfo_kh';
$where = ' WHERE zzx_balanceinfo_kh.dealtype BETWEEN 3 AND 5';
$res = $my_conn->query($sql . $where);
$pl_khls = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "客户流水表汇总（包括下单、平仓、异常）：$pl_khls\n";

// ZJ客户流水345gift汇总（包括下单、平仓、异常）
$sql_zj = 'SELECT Sum(zzx_balanceinfo_kh.gift) as pl FROM zzx_balanceinfo_kh';
$where = ' WHERE zzx_balanceinfo_kh.dealtype BETWEEN 3 AND 5';
$res = $my_conn->query($sql_zj . $where);
$pl_khls_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）：$pl_khls_zj\n";

// 客户流水345当日汇总（包括下单、平仓、异常）
$where .= " AND createtime >=$today_start";
$res = $my_conn->query($sql . $where);
$khls_today = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "客户流水表当日盈亏汇总：$khls_today\n";

// ZJ客户流水345gift当日汇总（包括下单、平仓、异常）
$where .= " AND createtime >=$today_start";
$res = $my_conn->query($sql_zj . $where);
$khls_today_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "ZJ客户流水表赠金当日盈亏汇总：$khls_today_zj\n";

// ZJ客户流水表下单支出
$where = ' WHERE zzx_balanceinfo_kh.dealtype = 3';
$res = $my_conn->query($sql_zj . $where);
$pl_khlsxd_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "ZJ客户流水表下单支出：$pl_khlsxd_zj\n";

// 客户流水表下单支出
$where = ' WHERE zzx_balanceinfo_kh.dealtype = 3';
$res = $my_conn->query($sql . $where);
$pl_khlsxd = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "客户流水表下单支出：$pl_khlsxd\n";

// 客户流水表平仓收入
$where = ' WHERE zzx_balanceinfo_kh.dealtype = 4';
$res = $my_conn->query($sql . $where);
$pl_khlspc = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "客户流水表平仓收入：$pl_khlspc\n";

// ZJ客户流水表平仓赠金支出
$where = ' WHERE zzx_balanceinfo_kh.dealtype = 4';
$res = $my_conn->query($sql_zj . $where);
$pl_khlspc_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "ZJ客户流水表平仓赠金支出：$pl_khlspc_zj\n";

// 客户流水表5异常退回
$where = ' WHERE zzx_balanceinfo_kh.dealtype = 5';
$res = $my_conn->query($sql . $where);
$pl_khlsxd_bak = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "客户流水表异常下单退回：$pl_khlsxd_bak\n";

// ZJ客户流水表5异常退回
$where = ' WHERE zzx_balanceinfo_kh.dealtype = 5';
$res = $my_conn->query($sql_zj . $where);
$pl_khlsxd_bak_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "ZJ客户流水表异常下单退回：$pl_khlsxd_bak_zj\n";



// 历史订单表的盈亏汇总
$sql = 'SELECT Sum(zzx_order_history.ploss) as pl FROM zzx_order_history';
$res = $my_conn->query($sql);
$pl_history = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "历史订单表的盈亏汇总：$pl_history\n";

// ZJ历史订单表的gift汇总
$sql_zj = 'SELECT Sum(zzx_order_history.gift) as pl FROM zzx_order_history';
$res = $my_conn->query($sql_zj);
$pl_history_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "ZJ历史订单表的gift汇总：$pl_history_zj\n";

// 异常订单表的盈亏汇总
$sql = 'SELECT Sum(zzx_order_error.money) as money FROM zzx_order_error';
$res = $my_conn->query($sql);
$money_err = mysqli_fetch_array($res, MYSQLI_ASSOC)['money'];
echo "异常订单表的金额汇总：$money_err\n";

// ZJ异常订单表的gift汇总
$sql = 'SELECT Sum(zzx_order_error.gift) as money FROM zzx_order_error';
$res = $my_conn->query($sql);
$money_err_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['money'];
echo "ZJ异常订单表的金额汇总：$money_err_zj\n";


// 机构表盈亏汇总
$sql = 'SELECT Sum(zzx_jg_company.gain_loss) as pl FROM zzx_jg_company';
$res = $my_conn->query($sql);
$pl_jq = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
echo "机构表盈亏汇总：$pl_jq\n";


// 客户表盈亏汇总（损金额汇总，盈利金额汇总）
$sql = 'SELECT Sum(zzx_wp_userinfo.losses) AS losses, Sum(zzx_wp_userinfo.gains) AS gains FROM zzx_wp_userinfo';
$res = $my_conn->query($sql);
$data = mysqli_fetch_array($res, MYSQLI_ASSOC);
$kh_losses = $data['losses'];
$kh_gains = $data['gains'];
echo "客户表亏损金额汇总：$kh_losses\n";
echo "客户表盈利金额汇总：$kh_gains\n";


// 验证 ##################################################################################
echo "\n验证：\n";

if ($fee_in) { // 内扣
    // 机构流水345汇总（收入汇总，返点平仓异常） = -客户流水345汇总（内扣）
    if (bccomp($jqls, -($pl_khls-($pl_khlsxd_zj+$pl_khlsxd_bak_zj)), 4) == 0) {
        echo "机构流水345汇总（收入汇总，返点平仓异常） = -客户流水345汇总：ok\n";
    } else {
        echo "机构流水345汇总（收入汇总，返点平仓异常）$jqls = -客户流水345汇总{$pl_khls}-(ZJ客户流水表下单支出{$pl_khlsxd_zj} + ZJ客户流水表异常下单退回{$pl_khlsxd_bak_zj})：error\n";
    }

} else { // 外扣
    // 机构流水4汇总（盈亏） = -客户流水345汇总（外扣）
    if (bccomp($pl_jqls, -($pl_khls-($pl_khlsxd_zj+$pl_khlsxd_bak_zj)), 4) == 0) {
        echo "机构流水4汇总（盈亏） = -客户流水345汇总 - (ZJ客户流水表下单支出 + ZJ客户流水表异常下单退回)：ok\n";
    } else {
        echo "机构流水4汇总（盈亏）$pl_jqls = -客户流水345汇总{$pl_khls}-(ZJ客户流水表下单支出{$pl_khlsxd_zj} + ZJ客户流水表异常下单退回{$pl_khlsxd_bak_zj})：error\n";
    }

}

if ($fee_in) {
    // 机构流水4汇总（盈亏）+ 机构流水35汇总（手续费） = -历史订单表的盈亏汇总（内扣）
    if (bccomp($pl_jqls + $fee, -($pl_history-$pl_khls_zj), 4) == 0) {
        echo "机构流水4汇总（盈亏）+ 机构流水35汇总（手续费） = -历史订单表的盈亏汇总+ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）：ok\n";
    } else {
        echo "机构流水4汇总（盈亏）$pl_jqls + 机构流水35汇总（手续费）{$fee} = -历史订单表的盈亏汇总{$pl_history}+ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）：{$pl_khls_zj}：error\n";
    }

} else {
    // 机构流水4汇总（盈亏） = -历史订单表的盈亏汇总（外扣）
    if (bccomp($pl_jqls, -($pl_history-$pl_khls_zj), 4) == 0) {
        echo "机构流水4汇总（盈亏） = -历史订单表的盈亏汇总 + ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）：ok\n";
    } else {
        echo "机构流水4汇总（盈亏） $pl_jqls = -历史订单表的盈亏汇总{$pl_history}+ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）：{$pl_khls_zj}：error\n";
    }
}

// 客户流水345汇总 = 历史订单盈亏汇总
if (bccomp($pl_khls, $pl_history-$pl_khlspc_zj, 4) == 0) {
    echo "客户流水345汇总 = 历史订单表的盈亏汇总 - ZJ客户流水表平仓赠金支出：ok\n";
} else {
    echo "客户流水345汇总 $pl_khls = 历史订单表的盈亏汇总{$pl_history}-ZJ客户流水表平仓赠金支出{$pl_khlspc_zj}：error\n";
}
// 客户流水5汇总 = 异常订单金额汇总
if (bccomp($pl_khlsxd_bak, $money_err, 4) == 0) {
    echo "客户流水5汇总 = 异常订单金额汇总：ok\n";
} else {
    echo "客户流水5汇总$pl_khlsxd_bak = 异常订单金额汇总{$money_err}：error\n";
}

if ($fee_in) {
    // 机构表盈亏汇总 + 机构流水35汇总（手续费） = 客户表亏损金额汇总 - 客户表盈利金额汇总（内扣）
    if (bccomp($pl_jq + $fee, $kh_losses - $kh_gains + $pl_khls_zj, 4) == 0) {
        echo "机构表盈亏汇总 + 机构流水35汇总（手续费） = 客户表亏损金额汇总 - 客户表盈利金额汇总（内扣）+ ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）：ok\n";
    } else {
        echo "机构表盈亏汇总{$pl_jq} + 机构流水35汇总（手续费）{$fee} = 客户表亏损金额汇总{$kh_losses} - 客户表盈利金额汇总（内扣）{$kh_gains}+ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）{$pl_khls_zj}：error\n";
    }

} else {
    // 机构表盈亏汇总 = 客户表亏损金额汇总 - 客户表盈利金额汇总（外扣）
    if (bccomp($pl_jq, $kh_losses - $kh_gains + $pl_khls_zj, 4) == 0) {
        echo "机构表盈亏汇总  = 客户表亏损金额汇总 - 客户表盈利金额汇总（外扣）+ ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）：ok\n";
    } else {
        echo "机构表盈亏汇总{$pl_jq} = 客户表亏损金额汇总{$kh_losses} - 客户表盈利金额汇总（外扣）{$kh_gains}+ZJ客户流水表赠金使用汇总（包括下单、平仓、异常）{$pl_khls_zj}：error\n";
    }
}

// 机构表盈亏汇总 = 机构流水4当日汇总
if (bccomp($pl_jq, $pl_jqls_today, 4) == 0) {
    echo "机构表盈亏汇总  = 机构流水4当日汇总：ok\n";
} else {
    echo "机构表盈亏汇总{$pl_jq} = 机构流水4当日汇总{$pl_jqls_today}：error\n";
}
// 客户表盈利金额汇总 - 客户表亏损金额汇总 = 客户流水345当日汇总
if (bccomp($kh_gains - $kh_losses - $pl_khlspc_zj, $khls_today, 4) == 0) {
    echo "客户表盈利金额汇总 - 客户表亏损金额汇总 - ZJ客户流水表平仓赠金支出  = 客户流水345当日汇总：ok\n";
} else {
    echo "客户表盈利金额汇总{$kh_gains} - 客户表亏损金额汇总{$kh_losses} - ZJ客户流水表平仓赠金支出{$pl_khlspc_zj}  = 客户流水345当日汇总{$khls_today}：error\n";
}




// 分层机构流水4盈亏汇总 = -相关历史订单盈亏汇总（外扣）
// 分层机构流水4盈亏汇总 = -相关历史订单盈亏汇总 - 相关订单的机构手续费（内扣）
if ($fee_in) {
    echo "\n分层机构流水4盈亏汇总  = -与交易的历史订单盈亏汇总 - 相关订单的机构手续费（内扣）：\n";
} else {
    echo "\n分层机构流水4盈亏汇总 = -与交易的历史订单盈亏汇总（外扣）：\n";
}
$sql = "SELECT c.id FROM zzx_jg_company AS c WHERE c.jg_type = 2 OR c.jg_type = 4";
$res = $my_conn->query($sql);
$total = 0;
while ($row = mysqli_fetch_assoc($res)) {
    $total += v_pl($my_conn, $row['id']);
}

/**
 * 分层机构盈亏汇总
 * @param $my_conn 数据库连接实例
 * @param $jid 机构ID
 */
function v_pl($my_conn, $jid)
{
    global $fee_in, $fee_ratio;
    // 机构盈亏汇总
    $where = " WHERE zzx_balanceinfo_jg.dealtype = 4 AND jid = $jid";
    $sql = "SELECT Sum(zzx_balanceinfo_jg.atm) as pl FROM zzx_balanceinfo_jg $where";
    $res = $my_conn->query($sql);
    $pl_jqls = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];
    
    if (empty($pl_jqls)) {
        return true;
    }

    // 相关历史订单盈亏汇总
    $sql = "SELECT Sum(o.ploss) as pl FROM zzx_order_history AS o WHERE o.member_id = $jid ";
    $res = $my_conn->query($sql);
    $pl_kh = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];

     // 相关历史订单手续费汇总
     $sql = "SELECT Sum(o.service_fee) as pl FROM zzx_order_history AS o WHERE o.member_id = $jid ";
     $res = $my_conn->query($sql);
     $fee_h = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];

    // 赠金流水，要减去
    // ZJ客户流水345gift汇总（包括下单、平仓、异常）
	$sql_zj = 'SELECT Sum(zzx_balanceinfo_kh.gift) as pl FROM zzx_balanceinfo_kh';
	$where = ' WHERE zzx_balanceinfo_kh.dealtype BETWEEN 3 AND 5 and zzx_balanceinfo_kh.jigou_id=' . $jid;
	$res = $my_conn->query($sql_zj . $where);
	$pl_fc_zj = mysqli_fetch_array($res, MYSQLI_ASSOC)['pl'];

    // 验证
    if ($fee_in) { // 内扣
        if (bccomp($pl_jqls, -$pl_kh  - $fee_h + $pl_fc_zj , 4) !== 0) {
            echo "{$pl_jqls} = -$pl_kh  - $fee_h + $pl_fc_zj error，jid：{$jid}\n\n";
        } else {
            echo "{$pl_jqls} = -$pl_kh  - $fee_h + $pl_fc_zj  ok\n";
        }

    } else {
        if (bccomp($pl_jqls , -($pl_kh - $pl_fc_zj), 4) !== 0) {
            echo "{$pl_jqls} = -{$pl_kh} error，jid：{$jid},赠金流水:{$pl_fc_zj} \n\n";
        } else {
            echo "{$pl_jqls} = -{$pl_kh}  - {$pl_fc_zj} ok \n";
        }
    }
}

echo "\n\n";
